package com.cennavi.mineplay.gisfile;

import java.io.File;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.geotools.data.FeatureSource;
import org.geotools.data.shapefile.ShapefileDataStore;
import org.geotools.feature.FeatureCollection;
import org.geotools.feature.FeatureIterator;
import org.locationtech.jts.geom.Geometry;
import org.opengis.feature.Property;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;

public class ShapeReader {

	private String createSql;

	private Connection conn;

	private String tableName;

	private Map<String, List<Object>> mapValues = new HashMap<>();
	
	private Map<String, String> tabColumnType = new HashMap<String, String>();

	public ShapeReader(String filePath, String tableName, Connection conn)
			throws Exception {

		this.tableName = tableName;

		this.conn = conn;

		ShapefileDataStore shpDataStore = null;

		shpDataStore = new ShapefileDataStore(new File(filePath).toURI()
				.toURL());
		shpDataStore.setCharset(Charset.forName("gbk"));
		String typeName = shpDataStore.getTypeNames()[0];
		FeatureSource<SimpleFeatureType, SimpleFeature> featureSource = null;
		featureSource = (FeatureSource<SimpleFeatureType, SimpleFeature>) shpDataStore
				.getFeatureSource(typeName);
		FeatureCollection<SimpleFeatureType, SimpleFeature> result = featureSource
				.getFeatures();
		FeatureIterator<SimpleFeature> itertor = result.features();

		if (itertor.hasNext()) {
			SimpleFeature feature = itertor.next();
			Collection<Property> p = feature.getProperties();
			Iterator<Property> it = p.iterator();
			while (it.hasNext()) {

				Property pro = it.next();

				if (pro.getName() != null
						&& pro.getName().toString().length() > 0 && pro.getName().toString().indexOf("geom")<0) {

					Object ov = pro.getValue();

					if (!(ov instanceof Geometry)) {
						String columnName = pro.getName().toString();

						if (ov instanceof Integer)
							tabColumnType.put(columnName, "int");
						else if (ov instanceof Long)
							tabColumnType.put(columnName, "int");
						else if (ov instanceof Double)
							tabColumnType.put(columnName, "numeric");
						else if (ov instanceof Float)
							tabColumnType.put(columnName, "numeric");
						else
							tabColumnType.put(columnName,
									"character varying(1000)");
					}  
				}
				
				tabColumnType.put("geom", "geometry");
			}
		}

		itertor = result.features();

		while (itertor.hasNext()) {
			SimpleFeature feature = itertor.next();
			Collection<Property> p = feature.getProperties();
			Iterator<Property> it = p.iterator();
			while (it.hasNext()) {

				Property pro = it.next();

				if (pro.getName() != null
						&& pro.getName().toString().length() > 0) {

					Object ov = pro.getValue();
					

					String columnName = pro.getName().toString();
					
					if (columnName.indexOf("geom")>=0)
						columnName = "geom";

					if (mapValues.get(columnName) == null) {
						mapValues.put(columnName, new ArrayList<Object>());
					}
					mapValues.get(columnName).add(ov);
				}
			}
		}

		StringBuilder sqlSB = new StringBuilder("create table " + tableName
				+ " (");

		Iterator<Entry<String, String>> it = tabColumnType.entrySet()
				.iterator();
		boolean isFirst = true;
		while (it.hasNext()) {
			if (isFirst) {
				isFirst = false;
			} else {
				sqlSB.append(",");
			}
			Entry<String, String> en = it.next();
			sqlSB.append(en.getKey());
			sqlSB.append(" ");
			sqlSB.append(en.getValue());

		}

		sqlSB.append(")");

		createSql = sqlSB.toString();

		try {
			itertor.close();
		} catch (Exception e) {
		}
	}

	public String getCreateSql() {
		return createSql;
	}

	public void createTable() throws Exception {
		Statement stmt = conn.createStatement();

		stmt.execute(this.getCreateSql());

		stmt.close();
	}

	public void batchInsert() throws Exception{
		
		StringBuilder sqlSB = new StringBuilder("insert into "+tableName+"(");
		
		String[] keys = new String[tabColumnType.size()];
		
		Iterator<Entry<String, String>> it = tabColumnType.entrySet()
				.iterator();
		boolean isFirst = true;
		
		String values = "";
		
		int idx = 0;
		
		while (it.hasNext()) {
			if (isFirst) {
				isFirst = false;
			} else {
				sqlSB.append(",");
				values += ",";
			}
			Entry<String, String> en = it.next();
			sqlSB.append(en.getKey());
			
			keys[idx++] = en.getKey();
			
			if ("geom".equals(en.getKey())){
				
				values += "st_geomfromtext(?,4326)";
				
			}else{
			
				values += "?";
			}
		}
		
		sqlSB.append(") values (");
		
		sqlSB.append(values);
		
		sqlSB.append(")");
		
		PreparedStatement pstmt = conn.prepareStatement(sqlSB.toString());
		
		int rowIndex = 0;
		
		while(rowIndex < mapValues.get("geom").size()){
			
			for( int i=0;i<keys.length;i++){
				
				String key = keys[i];
				
				Object value = mapValues.get(key).get(rowIndex);
				
				String type = tabColumnType.get(key);
				
				if (type.equals("int")){
					pstmt.setLong(i+1, Long.valueOf(value.toString()));
				}else if (type.equals("numeric")){
					pstmt.setDouble(i+1, (Double)value);
				}else if (type.startsWith("character")){
					if (value != null){
						String strValue = value.toString();
						if (strValue.length()>300)
						 pstmt.setString(i+1, strValue.substring(0, 300));
						else
						 pstmt.setString(i+1, strValue);	
					}else{
						pstmt.setString(i+1, null);
					}
				}else{
					pstmt.setString(i+1, value.toString());
				}
				
			}
			
			pstmt.addBatch();
			
			rowIndex++;
			
			if (rowIndex % 1000 == 0)
				pstmt.executeBatch();
			
		}
		
		pstmt.executeBatch();
		
		pstmt.close();
		
	}
	
	
//	public static void main(String[] args) throws Exception{
//		
//		Class.forName("org.postgresql.Driver");
//		
//		Connection conn = DriverManager.getConnection("jdbc:postgresql://36.111.84.170:5442/mineplay", "postgres", "cennavi2018");
//		
//		ShapeReader reader = new ShapeReader("C:\\Users\\lilei3774\\Desktop\\wuhan\\1\\POIwuhan_point.shx", "test_od", conn);
//		
//		reader.createTable();
//		
//		reader.batchInsert();
//		
//	}

}
